﻿@model Documentation.Models.DocModel

@{
    Layout = null;
}

<!DOCTYPE html>

<script type="text/javascript" src="~/Scripts/syntaxhighlighter/shCore.js"></script>
<script type="text/javascript" src="~/Scripts/syntaxhighlighter/shBrushCSharp.js"></script>

<link href="~/Content/syntaxhighlighter/shCore.css" rel="stylesheet" type="text/css" />
<link href="~/Content/syntaxhighlighter/shThemeDefault.css" rel="stylesheet" type="text/css" />

<html>
    <head>
        @{
            var title = "Query Lifting – safe textual queries";
        }
        <title>@title</title>
        <link rel="stylesheet" href="~/Content/Site.css" type="text/css" />
    </head>
    <body>
        <h1>@title</h1>
        
        @*//TODO AddWithValue для Nullable<T> наверное не работает. Перевести весь код на p.Value *@
        @*//TODO Правильно организовать ссылки в этом документе *@
        @*//TODO Проверить орфографию *@
        @*//TODO Разместить статью на внешнем ASP.NET хостинге. Подумать о поисковиках, они пока не нужны. *@
        @*//TODO Разобраться с Git. В итоге поместить проект в github. *@
        @*//TODO Разобраться с Nuget. Сделать nuget проект для CQ. *@

        <p>
            С одной стороны, определение запроса в виде строки является мощным и гибким средством для работы
            с базами данных, с другой стороны, такой код сложно писать, развивать и сопровождать, 
            по такому коду нет навигации. Для смягчения указанных недостатков код с текстовыми запросами
            покрывают unit тестами. Написание таких unit тестов это ручной рутинный труд.
            Человек может допустить ошибки при написании тестов.
            Нет гарантий, что тесты будут написаны на все варианты запросов.
        </p>
        <p>
            Возникают естественные вопросы. Почему рутинный труд не поручить машине?
            Для чего при решении этой задачи требуется человеческий мозг?
            Для ответов на эти вопросы разберем детали интеграции текстовых запросов
            и кода на языке общего назначения.
        </p>
        <p>
            Рассмотрим пример.
        </p>
        <script type="syntaxhighlighter" class="brush: csharp">
        <![CDATA[
        private static void Example1(DateTime? date)
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                using (var command = connection.CreateCommand())
                {
                    var builder = new StringBuilder(@@"
SELECT  Id, CreationDate
FROM    Post
WHERE   1 = 1");
                    if (date.HasValue)
                    {
                        builder.Append(@@"
        AND CreationDate >= @@Date");
                        command.Parameters.AddWithValue("@@Date", date);
                    }
                    command.CommandText = builder.ToString();
                    using (var reader = command.ExecuteReader())
                    {
                        var idOrdinal = reader.GetOrdinal("Id");
                        var creationDateOrdinal = reader.GetOrdinal("CreationDate");
                        while (reader.Read())
                        {
                            var id = reader.GetInt32(idOrdinal);
                            var creationDate = reader.GetDateTime(creationDateOrdinal);
                        }
                    }
                }
            }
        }
        ]]>
    </script>
    <h2>1. Тип для результата</h2>
    <p>
        Пары [id, Int32] и [CreationDate, DateTime] можно представить в виде интерфейса.
    </p>
        <script type="syntaxhighlighter" class="brush: csharp">
        <![CDATA[
public interface IPostInfo
{
    int Id { get; }
    DateTime CreationDate { get; }
}
        ]]>
    </script>
    <p>
        Тогда, используя runtime кодогенерацию
        (см. <a href="http://msdn.microsoft.com/en-us/library/system.reflection.emit.aspx">Emit</a>),
        строчки 21-27 можно переписать следующим образом.
    </p>
        <script type="syntaxhighlighter" class="brush: csharp">
        <![CDATA[
        private static void Example1(DateTime? date)
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                using (var command = connection.CreateCommand())
                {
                    var builder = new StringBuilder(@@"
SELECT  Id, CreationDate
FROM    Post
WHERE   1 = 1");
                    if (date.HasValue)
                    {
                        builder.Append(@@"
        AND CreationDate >= @@Date");
                        command.Parameters.AddWithValue("@@Date", date);
                    }
                    command.CommandText = builder.ToString();
                    using (var reader = command.ExecuteReader())
                    {
                        var ordinals = reader.GetOrdinals<IPostInfo>();
                        while (reader.Read())
                        {
                            IPostInfo postInfo = ordinals.Materialize(reader);
                        }
                    }
                }
            }
        }
        ]]>
    </script>
    <h2 id="sec2">2. Тестирование посредством SQL метаданных</h2>
    <p>
        Сделаем преобразование кода, извлечем метод <code>Example1Query</code>
        (<a href="http://www.jetbrains.com/resharper/webhelp50/Refactorings__Extract_Method.html">Extract Method</a>).
    </p>
    <p id="ref1">Листинг 1.</p>
    <script type="syntaxhighlighter" class="brush: csharp">
        <![CDATA[
        private static void Example1(DateTime? date)
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                using (var command = connection.CreateCommand())
                {
                    var prototype = Example1Query(date, command);
                    using (var reader = command.ExecuteReader())
                    {
                        var ordinals = reader.GetOrdinals(prototype);
                        while (reader.Read())
                        {
                            IPostInfo postInfo = ordinals.Materialize(reader);
                        }
                    }
                }
            }
        }

        private static IPostInfo Example1Query(DateTime? date, SqlCommand command)
        {
            var builder = new StringBuilder(@@"
SELECT  Id, CreationDate
FROM    Post
WHERE   1 = 1");
            if (date.HasValue)
            {
                builder.Append(@@"
        AND CreationDate >= @@Date");
                command.Parameters.AddWithValue("@@Date", date);
            }
            command.CommandText = builder.ToString();
            return default(IPostInfo);
        }
        ]]>
    </script>
    <p>
        Для метода <code>Example1Query</code> можно написать unit тест,
        который выполнит SQL запрос в режиме 
        <a href="http://msdn.microsoft.com/en-us/library/system.data.commandbehavior.aspx"
           ><code>SchemaOnly</code></a>.
        ADO.NET предоставляет метод
        <a href="http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.getschematable(v=vs.110).aspx"
           ><code>SqlDataReader.GetSchemaTable</code></a>, который возвращает метаданные колонок.
        В тесте ниже сравниваются метаданные колонок со свойствами интерфейса <code>IPostInfo</code>.
    </p>
        <script type="syntaxhighlighter" class="brush: csharp">
        <![CDATA[
        [TestMethod]
        public void Example1Test()
        {
            MetadataTest(command => Program.Example1Query(null, command));
            MetadataTest(command => Program.Example1Query(new DateTime(2000, 1, 1), command));
        }

        private static void MetadataTest<T>(Func<SqlCommand, T> func)
        {
            using (var connection = new SqlConnection(Program.ConnectionString))
            {
                connection.Open();
                using (var command = connection.CreateCommand())
                {
                    func(command);
                    using (var reader = command.ExecuteReader(CommandBehavior.SchemaOnly))
                    {
                        var expected = reader.GetSchemaTable().Rows.Cast<DataRow>().Select(row => new {
                            Name = (string) row["ColumnName"],
                            Type = GetType(row)
                        });
                        var actual = typeof (T).GetProperties().Select(info => new {
                            info.Name,
                            Type = info.PropertyType
                        });
                        if (expected.Except(actual).Any() || actual.Except(expected).Any())
                            throw new ApplicationException(@@"Expected type:
public interface " + typeof(T).Name + @@"
{
" + string.Join(@@"
", expected.Select(_ => @@"    " + _.Type.ToCSharp() + @@" " + _.Name + @@" { get; }")) + @@"
}");
                    }
                }
            }
        }

        private static Type GetType(DataRow row)
        {
            var type = (Type) row["DataType"];
            return type.IsClass || false.Equals(row["AllowDBNull"])
                ? type
                : typeof (Nullable<>).MakeGenericType(type);
        }
        ]]>
    </script>
    <p>
        Если метаданные колонок не совпадут с интерфейсом <code>IPostInfo</code>,
        то в тексте сообщения будет указан полный текст ожидаемого интерфейса.
        Таким образом, текст интерфейса <code>IPostInfo</code>
        генерируется автоматически на основе текста SQL запроса.
    </p>
    <h2 id="sec3">3. Тестовые значения параметров</h2>
    <p>
        В тесте в параметр <code>date</code> метода <code>Example1Query</code>
        передаются значения <code>null</code> и <code>new DateTime(2000, 1, 1)</code>.
        Для теста важно лишь <code>date.HasValue</code>, а конкретное значение
        <code>date.Value</code> не используется в тесте. В режиме
        <a href="http://msdn.microsoft.com/en-us/library/system.data.commandbehavior.aspx"
        ><code>SchemaOnly</code></a> для выполнения запроса значения параметров не используются.
    </p>
    <p>
        Введем правила для типов параметров. Например, для параметров типа <code>DateTime?</code>
        подставляем значения <code>null</code> и <code>new DateTime(2000, 1, 1)</code>.
        Для типа <code>bool</code> – <code>true</code>, <code>false</code>.
        Для <code>enum</code> типов – все члены <code>enum</code>.
        Для типа <code>string</code> – <code>null</code> и <code>"test string"</code>.
        Для типа <code>Func&lt;T&gt;</code> – <code>() =&gt; TValue</code>, где
        <code>TValue</code> принимает значения из множества значений для типа T.
        Аналогично для типов <a href="http://msdn.microsoft.com/en-us/library/dd233245.aspx"
            ><code>Option</code></a> и <a href="http://msdn.microsoft.com/en-us/library/ee353439.aspx"
            ><code>Choice</code></a>.
        И т.д.
        Тогда тест будет состоять из одной строчки.
    </p>
    <script type="syntaxhighlighter" class="brush: csharp">
        <![CDATA[
        [TestMethod]
        public void Example1Test()
        {
            MetadataTest(typeof(Program), "Example1Query");
        }
        ]]>
    </script>
    <p>
        Если у тестируемого метода (<code>Example1Query</code>) будет несколько
        параметров, метод <code>MetadataTest</code> переберет все комбинации значений параметров.
    </p>
    <h2 id="sec4">4. Автоматический поиск методов для тестирования</h2>
    <p>
        Для полной автоматизации осталось избавиться в тесте от указания метода 
        <code>Example1Query</code>. Введем правило. Текст запроса всегда выставляется
        посредством вызова сеттера
        <a href="http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtext(v=vs.110).aspx"
           ><code>SqlCommand.CommandText</code></a>.
        С помощью методов
        <a href="http://msdn.microsoft.com/en-us/library/system.reflection.methodbase.getmethodbody(v=vs.110).aspx">GetMethodBody</a>
        и <a href="http://msdn.microsoft.com/en-us/library/ms145418(v=vs.110).aspx">ResolveMember</a>
        можно найти все методы, в теле которых вызывается
        сеттер <a href="http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtext(v=vs.110).aspx"
           ><code>SqlCommand.CommandText</code></a>.
        Теперь тест выглядит следующим образом.
    </p>
    <script type="syntaxhighlighter" class="brush: csharp">
        <![CDATA[
        [TestMethod]
        public void CheckAllQueries()
        {
            QueryChecker.CheckAllQueries(ConnectionString, GetType().Assembly.GetTypes());
        }
        ]]>
    </script>
    <p>
        Тест находит все методы, в теле которых вызывается сеттер
        <a href="http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtext(v=vs.110).aspx"
           ><code>SqlCommand.CommandText</code></a> и
        для каждого найденного метода вызывает метод <code>MetadataTest</code>.
    </p>
    <h2 id="sec5">5. Код запроса внутри лямбда выражения</h2>
    <p>
        Теперь методы ищутся автоматически по IL коду, поэтому
        в C# коде метод с запросом может быть представлен в виде метода без имени – 
        лямбда выражения. Введем метод <code>Apply</code>.
    </p>
    <script type="syntaxhighlighter" class="brush: csharp">
        <![CDATA[
public static TResult Apply<T, TResult>(this T arg, Func<T, TResult> func)
{
    return func(arg);
}        
        ]]>
    </script>
    <p>
        Тогда <a href="#ref1">Листинг 1.</a> можно переписать следующим образом.
    </p>
    <script type="syntaxhighlighter" class="brush: csharp">
        <![CDATA[
        private static void Example1(DateTime? date)
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                using (var command = connection.CreateCommand())
                {
                    var prototype = new {date, command}.Apply(p => {
                        var builder = new StringBuilder(@@"
SELECT  Id, CreationDate
FROM    Post
WHERE   1 = 1");
                        if (p.date.HasValue)
                        {
                            builder.Append(@@"
        AND CreationDate >= @@Date");
                            p.command.Parameters.AddWithValue("@@Date", p.date);
                        }
                        p.command.CommandText = builder.ToString();
                        return default(IPostInfo);
                    });
                    using (var reader = command.ExecuteReader())
                    {
                        var ordinals = reader.GetOrdinals(prototype);
                        while (reader.Read())
                        {
                            IPostInfo postInfo = ordinals.Materialize(reader);
                        }
                    }
                }
            }
        }
        ]]>
    </script>
    <p>
        Если нет замыканий, то лямбда выражение компилируется в статический метод,
        поэтому метод <code>CheckAllQueries</code> может его вызвать и протестировать.
        Если замыкания есть, метод <code>CheckAllQueries</code> выдаст сообщение о невозможности
        автоматически протестировать этот запрос.
    </p>
    <h2 id="sec6">6. Helper Methods</h2>
    <p>
        Для удобства введем следующие методы.
    </p>
    <script type="syntaxhighlighter" class="brush: csharp">
        <![CDATA[
public static IEnumerable<T> List<T>(this Func<SqlCommand, T> it)
{
    return it.Result().ToList();
}

public static IEnumerable<T> Result<T>(this Func<SqlCommand, T> it)
{
    using (var connection = new SqlConnection(Program.ConnectionString))
    {
        connection.Open();
        using (var command = connection.CreateCommand())
        {
            var prototype = it(command);
            using (var reader = command.ExecuteReader())
            {
                var ordinals = reader.GetOrdinals(prototype);
                while (reader.Read())
                    yield return ordinals.Materialize(reader);
            }
        }
    }            
}
        ]]>
    </script>
    <p>
        Тогда наш пример записывается следующим образом.
    </p>
    <p id="ref1_1">Листинг 2.</p>
    <script type="syntaxhighlighter" class="brush: csharp">
        <![CDATA[
        private static void Example1(DateTime? date)
        {
            var postInfos = new {date}.Apply((с, p) => {
                var builder = new StringBuilder(@@"
SELECT  Id, CreationDate
FROM    Post
WHERE   1 = 1");
                if (p.date.HasValue)
                {
                    builder.Append(@@"
        AND CreationDate >= @@Date");
                    с.Parameters.AddWithValue("@@Date", p.date);
                }
                с.CommandText = builder.ToString();
                return default(IPostInfo);
            }).List();
        }
        ]]>
    </script>
    <h2 id="sec9">7. Краткий синтаксис посредством Razor</h2>
    <p>
        Для смешивания C# кода и текста удобно воспользоваться
        <a href="http://weblogs.asp.net/scottgu/archive/2010/07/02/introducing-razor.aspx">Razor</a>.
        <a href="http://vibrantcode.com/blog/2010/11/16/hosting-razor-outside-of-aspnet-revised-for-mvc3-rc.html"
           >Razor может хоститься вне ASP.NET.</a>. 
        <a href="#ref1_1">Листинг 2</a> переписывается следующим образом.
    </p>
    <p id="ref3">Листинг 3.</p>
    <script type="syntaxhighlighter" class="brush: csharp">
        <![CDATA[
        private static void Example1(DateTime? date)
        {
            var postInfos = new {date}.Apply((с, p) => {
                c.CommandText = p.QFragment(@@"
SELECT  Id, CreationDate
FROM    Post
WHERE   1 = 1
@@if (date.HasValue) {
        @@:AND CreationDate >= @@(new{date})
}").ToString(c);
                return default(IPostInfo);
            }).List();
        }
        ]]>
    </script>
    <p>
        Razor переводит строку
    </p>
    <pre>
SELECT  Id, CreationDate
FROM    Post
WHERE   1 = 1
@@if (date.HasValue) {
        @@:AND CreationDate >= @@date
}</pre> в следующий C# код
    <script type="syntaxhighlighter" class="brush: csharp">
        <![CDATA[
    public partial class Tbaccea68f4e64e3d837b34c2cb5115ad : TemplateBase {        
        public override void Execute() {
WriteLiteral("\r\nSELECT  Id, CreationDate\r\nFROM    Post\r\nWHERE   1 = 1\r\n");            
 if (date.HasValue) {
WriteLiteral("        ");
WriteLiteral("AND CreationDate >= ");
                         Write(date);
WriteLiteral("\r\n");
}
        }
    }
        ]]>
    </script>
    <p>
        В перегруженном (overloading) методе <code>Write</code> происходит добавление параметра в коллекцию
    </p>
    <script type="syntaxhighlighter" class="brush: csharp">
        <![CDATA[
        protected void Write(DateTime? arg)
        {
            ...
            command.Parameters.AddWithValue("@@" + command.Parameters.Count, arg);
            ...
        }
        ]]>
    </script>
    <p>
        При каждом вызове метода <code>Write</code> происходит создание нового SQL параметра.
    </p>
    <p>
        Если параметр встречается в Razor шаблоне несколько раз, а SQL параметр требуется один,
        то необходимо создать экземпляр <code>DbParam</code>.
    </p>
    <script type="syntaxhighlighter" class="brush: csharp">
        <![CDATA[
                c.CommandText = p.QFragment(@@"
...
@@if (search.HasValue) {
        var searchParam = search.DbParam();
        @@:AND (SourceTagName LIKE @@searchParam OR TargetTagName LIKE @@searchParam)
}").ToString(c);
        }
        ]]>
    </script>
    <a href="#ref3">Листинг 3</a> можно переписать еще в более краткой форме.
    <p id="ref4">Листинг 4.</p>
    <script type="syntaxhighlighter" class="brush: csharp">
        <![CDATA[
        private static void Example1(DateTime? date)
        {
            var posts = Query<IPostInfo>.New(new {date}, @@"
SELECT  Id, CreationDate
FROM    Post
WHERE   1 = 1
@@if (date.HasValue) {
        @@:AND CreationDate >= @@date
}").List();
        }        ]]>
    </script>
    <h2 id="sec10">8. Find Usages для сущностей базы данных: таблиц, колонок и т.д.</h2>
    <p>
        Метод <code>CheckAllQueries</code> перебирает все варианты запросов.
        Временно создав хранимую процедуру на основе текста запроса,
        с помощью процедуры <a href="http://technet.microsoft.com/en-us/library/bb677185.aspx"
            ><code>sys.dm_sql_referenced_entities</code></a>
        можно узнать какие сущности базы данных используются в запросе.
        Такой алгоритм используюется в методе <code>FindUsages</code>.
    </p>
    <p>
        Пример использования метода <code>FindUsages</code>.
    </p>
    <script type="syntaxhighlighter" class="brush: csharp">
        <![CDATA[
FindUsages(tableName: "Post", columnName: "CreationDate")
        ]]>
    </script>
    <p>
        Возвращаемый результат.
    </p>
    <script type="syntaxhighlighter" class="brush: csharp">
        <![CDATA[
C:\...\Source\ControllableQuery.Demo\SomeClass1.cs (Ln 15)
C:\...\Source\ControllableQuery.Demo\SomeClass2.cs (Ln 27)
C:\...\Source\ControllableQuery.Demo\SomeClass2.cs (Ln 103)
        ]]>
    </script>
    <p>
        Указаны файл, номер строки и номер столбца в файле, которые указывают на начало метода.
        Эти методы генерируют запросы, в которых используется
        колонка <code>Post.CreationDate</code>.
    </p>
    <h2>9. INSERT, UPDATE, DELETE</h2>
    <p>
        Примеры выполнение операций <code>INSERT</code> и <code>UPDATE</code>.
    </p>
    <script type="syntaxhighlighter" class="brush: csharp">
        <![CDATA[
            var table = new Table.Book();
            var p = new {
                request.Name, request.Author, request.Isbn, LibraryId = int.Parse(request.LibraryId)
            };
            (isNew
                ? NonQuery.New(new {table, p}, "@@p.Insert(table)")
                : NonQuery.New(new {table, p, key = new {BookId = int.Parse(request.BookId)}}, "@@p.Update(key, table)")
                ).Execute();
        ]]>
    </script>
    <p>
        Если в базе данных добавить колонку в таблицу <code>Book</code>,
        то тест <code>CheckAllQueries</code> проинформирует о том,
        что в переменную <code>p</code> нужно добавить значение для новой колонки.
    </p>
    <p>
        Выполнение <code>DELETE</code>.
    </p>
    <script type="syntaxhighlighter" class="brush: csharp">
        <![CDATA[
NonQuery.New(new {BookId = int.Parse(bookId)}, "@@p.Delete(\"Book\")").Execute();
        ]]>
    </script>
    <script type="text/javascript">
        SyntaxHighlighter.all()
    </script>
    </body>
</html>